| 翻译：MariaDB ALTER TABLE语句 | 您所在的位置：网站首页 › mariadb 引擎 aria › 翻译：MariaDB ALTER TABLE语句 |
|
本文是MariaDB官方ALTER TABLE手册的翻译，99%的内容是按照手册给的内容进行翻译的。javascript 原文地址：https://mariadb.com/kb/en/library/alter-table/php 我提交到MariaDB官方手册的译文：https://mariadb.com/kb/zh-cn/library/alter-table/css Syntax ALTER [ONLINE] [IGNORE] TABLE tbl_name [WAIT n | NOWAIT] alter_specification [, alter_specification] ... alter_specification: table_option ... | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ] | ADD [COLUMN] (col_name column_definition,...) | ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ... | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ... | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ... | ADD FULLTEXT [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ... | ADD SPATIAL [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ... | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition | ALTER [COLUMN] col_name SET DEFAULT literal | (expression) | ALTER [COLUMN] col_name DROP DEFAULT | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] | DROP [COLUMN] [IF EXISTS] col_name [RESTRICT|CASCADE] | DROP PRIMARY KEY | DROP {INDEX|KEY} index_name | DROP FOREIGN KEY fk_symbol | DROP CONSTRAINT constraint_name | DISABLE KEYS | ENABLE KEYS | RENAME [TO] new_tbl_name | ORDER BY col_name [, col_name] ... | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] | [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name | DISCARD TABLESPACE | IMPORT TABLESPACE | ALGORITHM [=] {DEFAULT|INPLACE|COPY} | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE} | FORCE | partition_options | ADD PARTITION (partition_definition) | DROP PARTITION partition_names | COALESCE PARTITION number | REORGANIZE PARTITION [partition_names INTO (partition_definitions)] | ANALYZE PARTITION partition_names | CHECK PARTITION partition_names | OPTIMIZE PARTITION partition_names | REBUILD PARTITION partition_names | REPAIR PARTITION partition_names | EXCHANGE PARTITION partition_name WITH TABLE tbl_name | REMOVE PARTITIONING column_definition: data_type [NOT NULL | NULL] [DEFAULT default_value | (expression)] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}] [STORAGE {DISK|MEMORY|DEFAULT}] | data_type [GENERATED ALWAYS] AS ( ) {VIRTUAL | PERSISTENT} [UNIQUE] [UNIQUE KEY] [COMMENT 'string'] index_col_name: col_name [(length)] [ASC | DESC] index_type: USING {BTREE | HASH | RTREE} index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string' | CLUSTERING={YES| NO} table_options: table_option [[,] table_option] ... (see CREATE TABLE options)从MariaDB 10.0.2开始，ALTER TABLE还支持IF EXISTS和IF NOT EXISTS字句。包括如下几种状况：html ADD COLUMN [IF NOT EXISTS] ADD INDEX [IF NOT EXISTS] ADD FOREIGN KEY [IF NOT EXISTS] ADD PARTITION [IF NOT EXISTS] CREATE INDEX [IF NOT EXISTS] DROP COLUMN [IF EXISTS] DROP INDEX [IF EXISTS] DROP FOREIGN KEY [IF EXISTS] DROP PARTITION [IF EXISTS] CHANGE COLUMN [IF EXISTS] MODIFY COLUMN [IF EXISTS] DROP INDEX [IF EXISTS]当使用了IF EXISTS或IF NOT EXISTS时，当知足存在或不存在的条件时，查询将不会产生任何错误。java 基本描述ALTER TABLE语句能够改变已存在表的结构。例如，能够增减字段、建立或销毁索引、修改字段类型、重命名字段或重命名表自身。还能够改变表的注释以及表的存储引擎。mysql 若是有某个链接正在使用表，将会激活一个元数据锁，这使得ALTER语句会一直等待直到该锁被释放。这也适用于非事务表。nginx 当在已存在重复值的某字段(或某几个字段)上创建UNIQUE索引时，将生成一个错误信息，而后alter语句终止。能够指定IGNORE选项忽略字段中的重复值，以禁止该错误并强制建立UNIQUE索引。但它将没法控制已存在的行。还须要注意，alter table ... exchange partition语句虽然接受IGNORE选项，但会忽略它。git ALTER TABLE能够重命名表，详细信息见个人另外一篇译文RENAME TABLE。github 当建立索引时，存储引擎将在处理过程当中使用可配置的buffer。增大buffer能够加速索引的建立。Aria和MyISAM存储引擎分别根据aria_sort_buffer_size和myisam_sort_buffer_size指定的值大小来分配buffer，在REPAIR TABLE时也一样会使用其值来分配buffer空间。InnoDB/XtraDB存储引擎使用innodb_sort_buffer_size的值来分配3个具备该值大小的buffer空间。web 注：innodb sort buffer的大小影响索引建立速度，还影响online DDL操做时记录并发写的临时日志文件数量。在MariaDB 10.0之前，innodb_sort_buffer_size的大小是固定不可配置的，其值为1M。设置的值越大，在排序时合并结果的次数和阶段就越少，速度就越快。当CREATE TABLE或ALTER TABLE建立新的索引时，将分配3个具备该值大小的buffer空间，还包括缓冲区中的行的指针。该变量为全局变量，单位为字节，默认值为1048576(即1M)，有效范围为65536-67108864(即64k-64M)。 索引类型Index Type各存储引擎容许的索引类型以下： Storage Engine Permitted Indexes Aria BTREE, RTREE MyISAM BTREE, RTREE InnoDB BTREE MEMORY/HEAP HASH, BTREE NDB BTREE, HASHCLUSTERING={YES|NO}仅对Tokudb有效。 CREATE INDEX和DROP INDEX一样也能够添加或删除索引。 字符集和排序规则Character Sets and Collations CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] [DEFAULT] CHARACTER SET [=] charset_name [DEFAULT] COLLATE [=] collation_name WAIT/NOWAIT从MariaDB 10.3.0开始引入WAIT和NOWAIT选项，用于设置某些语句的锁等待超时时长。详细信息见个人另外一篇译文：MariaDB wait/nowait。 ADD COLUMN [IF NOT EXISTS] (col_name column_definition,...)向表中添加一个字段。语法同CREATE TABLE。若是使用了IF NOT EXISTS，则待添加的列已存在时不会被建立。这在脚本中想要修改表时很是有用。 FIRST和AFTER字句会影响数据文件datafile中字段的物理顺序。使用FIRST以将字段添加到表的最左边的位置，即做为第一列。或者使用AFTER使得新建的列在指定的字段以后。注意，直到目前为止，字段的物理位置顺序一般是可有可无的。 DROP COLUMN [IF EXISTS] col_name [CASCADE|RESTRICT]从表中删除字段。若是使用IF EXISTS，那么在字段不存在时不会产生错误信息。若是字段是某个或某些索引的一部分，删除字段将会从索引中将其删除，除非你在同一时刻建立一个同名的新字段(例如修改字段类型时会隐式重建字段)。若是索引中的全部字段都被删除了，则索引会自动被删除。若是在视图或触发器中引用了某个字段，将在下次访问视图或触发器时产生一个错误信息。 从MariaDB 10.2.8开始，从多列复合的UNIQUE约束中删除某个字段是不被容许的，例如： CREATE TABLE a ( a int, b int, primary key (a,b) ); ALTER TABLE x DROP COLUMN a; [42000][1072] Key column 'A' doesn't exist in table删除字段a的过程当中将致使新的约束(删除字段a就变成了新约束)要求字段b中的全部值都是惟一的。要删除UNIQUE索引中的字段，须要显式指定drop primary key以及add primary key。而MariaDB 10.2.7版本及之前的版本，均可以直接删除字段并应用新的约束，以下： ALTER TABLE x DROP COLUMN a; Query OK, 0 rows affected (0.46 sec) DESC x; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | b | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+RESTRICT和CASCADE使得从其余数据库系统移植数据更加容易，但在MariaDB中，它们没有任何做用。 MODIFY COLUMN能够用来修改字段类型。modify操做不会改变字段顺序，也不会影响索引中的字段元素。注意，当modifiy字段时，必须从新指定新字段的全部属性，但由于不会影响索引中的内容，因此无需再指定约束类属性。 CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY((a)); ALTER TABLE t1 MODIFY a BIGINT UNSIGNED AUTO_INCREMENT; CHANGE COLUMNchange和modify工做方式基本相同，除了change能够改变字段名称。一样不会改变字段顺序和索引中的字段元素。 CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(a)); ALTER TABLE t1 CHANGE a b BIGINT UNSIGNED AUTO_INCREMENT; ENABLE/ DISABLE KEYSDISABLE KEYS将无视表中存储引擎(至少是MyISAM和Aria)支持的全部非unique索引。这能够加速向空表插入数据。 ENABLE KEYS用来启用全部被DISABLE的KEYS。 ENGINE/FORCE For Re-building a TableALTER TABLE能够强制MariaDB重建(rebuild)表。在MariaDB 10.0之前，只能经过设置表的ENGINE为原值来实现。从MariaDB 10.0开始，还可使用FORCE选项来实现。例以下面的InnoDB表，能够： ALTER TABLE tab_name ENGINE = InnoDB;从MariaDB 10.0开始，这等价于： ALTER TABLE tab_name FORCE;对于InnoDB存储引擎，ALTER TABLE将在innodb_file_per_table设置为ON时回收未使用的空间(例如，以前删除行后遗留下来的空间)。若是该变量的值为OFF，在ALTER TABLE后将不会回收未使用的空间，但新插入数据时能够从新使用这些空间。 IMPORT TABLESPACE用于导入由flush tables for export建立的InnoDB表。 导入的过程大体以下： CREATE TABLE t…; /* using the output from SHOW CREATE TABLE */ ALTER TABLE t DISCARD TABLESPACE; /* effectively corrupts the database! */ /* copy the file t.ibd to the file system */ ALTER TABLE t IMPORT TABLESPACE;其余存储引擎无需使用alter table ...import。这些数据被拷贝后能够马上被成功访问。 ALGORITHM子句/ALTER TABLE什么时候会拷贝全表数据?在MariaDB 10.0以前，ALTER TABLE操做表时会建立该表的临时副本，这使得操做大表时速度较慢。从MariaDB 10.0开始，ALTER TABLE的许多操做均可以直接在原地操做，再也不须要建立表的临时副本。 随着时间的推移，可能愈来愈多的操做都再也不须要拷贝全表数据。如下是目前不须要表拷贝的动做的alter table行为： 修改字段名称。 扩大整型数据类型的显示宽度，例如INT(2)-->INT(3)。 修改表注释。 向enum的列表尾部添加值。 重命名表。若是要执行多个操做，而且其中可能有一个或多个操做要求重建表，那么能够很方便地将这些操做组合在单个ALTER TABLE语句中，以便只执行一次重建操做。 从MariaDB 10.0开始，ALTER TABLE开始支持ALGORITHM字句，该字句有3种值： ALGORITHM=DEFAULT (未指定ALGORITHM子句时的默认值) ALGORITHM=COPY ALGORITHM=INPLACE设置ALGORITHM=COPY时，即便是那些不必拷贝表数据的操做也会进行copy。这会致使大量表数据的拷贝。 设置ALGORITHM=INPLACE时，将采用in-place技术(原地执行操做)，它会禁止表数据拷贝。若是某操做要求拷贝表数据，那么会返回相似以下的错误： CREATE TABLE t1 (a INT, e ENUM ('red','green')); ALTER TABLE t1 MODIFY e EMUM('red','green','blue'), ALGORITHM=INPLACE; -> Query OK, 0 rows affected (0.11 sec) -> Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE t1 ADD c INT, ALGORITHM=INPLACE; -> ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.默认的行为(ALGORITHM=DEFAULT,或未设置ALGORITHM子句)一般仅在须要拷贝时进行表数据拷贝。能够经过设置系统变量old_alter_table的值为ON(默认为OFF)来改变它的行为，这种状况下将使用pre-MySQL 5.0的拷贝算法进行表拷贝。 当设置ALGORITHM=INPLACE算法时，某些状况下会须要一些临时文件，这些临时文件建立在tmpdir系统变量指定的临时目录内。 注意，若是使用了COPY算法，那么innodb_file_per_table和innodb_file_format变量的当前值会在InnoDB表重建时被提交。 LOCK子句不一样的操做、不一样的存储引擎，ALTER TABLE采用的锁策略也不一样。在某些状况下彻底不须要任何锁，某些状况下仅须要读锁，某些状况下又须要写锁。LOCK子句能够指定一个固定的锁策略。它会强制使用该锁策略(即便指定的锁策略可能比操做正常需求的锁策略更严格)，但若是存储引擎上的某个操做要求比指定的策略更严格的策略，将生成错误信息。LOCK接受的值包括： DEFAULT: 采用所容许的最高并发级别的锁。 NONE: 不采用任何锁，这可能会常常性地产生错误信息。 SHARED: 采用读锁。 EXCLUSIVE: 采用写锁。 不指定LOCK子句时，默认LOCK=DEFAULT。此外，可使用ALTER ONLINE TABLE确保ALTER TABLE子句不会阻塞任何正在并发的操做(不使用任何锁)，这等价于LOCK=NONE。 显示进度从MariaDB 5.3开始，能够在支持进度报告协议的客户端上获取ALTER TABLE的处理进度。例如，从mysql客户端： ALTER TABLE test ENGINE=Aria; Stage: 1 of 2 'copy to tmp table' 46% of stageshow processlist以及information_schema.processlist表一样也能够显示处理进度。 权限需求ALTER TABLE要求至少有ALTER权限。重命名表还须要DROP, CREATE以及INSERT权限(由于重命名表是重建表的过程，须要拷贝整个表数据到临时副本，并使用副本填充新表)。 示例添加一个字段： ALTER TABLE t1 ADD x INT;删除一个字段： ALTER TABLE t1 DROP x;修改字段类型： ALTER TABLE t1 MODIFY x bigint unsigned;修改字段名称以及字段类型： ALTER TABLE t1 CHANGE a b bigint unsigned auto_increment;整合多个子句到单个ALTER TABLE语句中，使用逗号分隔： ALTER TABLE t1 DROP x, ADD x2 INT, CHANGE y y2 INT;更换存储引擎： ALTER TABLE t1 ENGINE = InnoDB;强制重建表(若是上面的例子中存储引擎已是InnoDB，也将会重建表)： ALTER TABLE t1 FORCE; |
| CopyRight 2018-2019 实验室设备网 版权所有 |